import os
import re
import xlrd
from pathlib import Path
from openpyxl import *
from common.log import logger
from config.config import DATA_PATH

support_file = ".xlsx"


class FileFormatError(Exception):
    def __init__(self, error_info):
        self.error_info = error_info

    def __str__(self):
        logger.error(f"Can't operate the excel file with postfix '{self.error_info}'")
        logger.error(f"{self.__class__.__name__} class only supports {support_file} file")


class ExcelOperate():
    def __init__(self, file_path=str(Path(DATA_PATH) / "test.xlsx")):
        """
        :param file_path: excel文件路径，包括excel文件名
        """
        if os.path.exists(file_path):
            self.file_path = str(file_path)
        else:
            logger.error(f"{file_path} dosesn't exist.")
            raise FileNotFoundError(f"{file_path} dosesn't exist.")
        object_compile = re.compile(r".(\w+)$", flags=re.IGNORECASE)
        object_match = object_compile.search(self.file_path)
        if object_match.group(1) != "xlsx":
            raise FileFormatError(f"{object_match.group()}")
        workbook = xlrd.open_workbook(file_path)
        self.sheet_names: list = workbook.sheet_names()

    def read_row_data(self, sheet_name: str, start_row: int = 1, end_row: int = -1):
        """
        读取行数据
        :param sheet_name: 通过sheet页名定位到指定sheet页
        :param start_row: 读取行数据的开始位置，默认1表示从第一行开始读取，而非索引号
        :param end_row: 读取行数据的结束位置，默认-1表示读取数据到最后一行
        :return: 返回字典类型数据，其中，code表示执行结果代码，message表示执行结果详情，data表示待传递的数据，这里是元素为字典的列表
        """
        one_data = {}
        all_data = []
        try:
            workbook = xlrd.open_workbook(self.file_path)
            worksheet = workbook.sheet_by_name(sheet_name)
            rows = worksheet.nrows
            cols = worksheet.ncols
            if end_row == -1:
                end_row = rows
            for i in range(start_row - 1, end_row):
                for j in range(cols):
                    one_data[str(worksheet.cell(0, j).value)] = str(worksheet.cell(i, j).value)
                all_data.append(one_data)
                one_data = {}
            result = {"code": "000", "message": "读取excel文件数据成功", "data": all_data}
        except Exception as e:
            logger.error(f"{e}")
            logger.error(f"Failed to get data from excel file '{self.file_path}'")
            result = {"code": "900", "message": "读取excel文件数据失败", "data": []}
        return result

    def write_row_data(self, sheet_name: str, data: list, write_type: int = 0, start_row: int = 1):
        """
        列表类型数据按行插入
        支持一行或多行数据的写操作，支持追加或覆盖方式
        默认待写入列表的元素顺序与sheet页标题对应，默认从第一列开始写数据
        :param sheet_name: 通过sheet页名称定位到指定sheet页
        :param data: 待写入excel文件中的数据
        :param write_type: 写入方式是追加or覆盖？0-追加，1-覆盖
        :param start_row: 若是覆盖，从哪一行的数据开始清除
        :return: 返回字典类型数据，其中，code表示执行结果代码，message表示执行结果详情，data表示带传递的数据
        """
        try:
            """
            保证写入excel文件中的每个单元格数据格式是文本
            """
            workbook = load_workbook(self.file_path)
            """
            不再使用workbook.get_sheet_by_name()方法获取worksheet对象，因其已弃用，
            “DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).”
            """
            worksheet = workbook[sheet_name]
            if write_type == 0:
                """
                追加型写入
                """
                if type(data[0]) == list:
                    """
                    多行数据写入
                    """
                    for each in data:
                        each = [str(i) for i in each]
                        workbook.save(self.file_path)
                        worksheet.append(each)
                else:
                    """
                    单行数据写入
                    """
                    data = [str(each) for each in data]
                    worksheet.append(data)
            else:
                """
                覆盖型写入
                """
                rows = worksheet.max_row
                num_del = rows - 1
                data = [str(each) for each in data]
                worksheet.delete_rows(start_row, amount=num_del)
                if type(data[0]) == list:
                    for each in data:
                        worksheet.append(each)
                else:
                    worksheet.append(data)
            workbook.save(self.file_path)
            result = {"code": "000", "message": "写入行数据成功", "data": []}
        except Exception as e:
            logger.error(f"{e}")
            logger.error(f"Failed to write row data to excel file '{self.file_path}'")
            result = {"code": "900", "message": "写入行数据失败", "data": []}
        return result

    def locate_cell_position(self, sheet_name, value: str):
        """
        定位指定内容的所在位置
        :param sheet_name: sheet页名称，用于定位sheet页
        :param value: 待查找位置的数据
        :return: 返回字典类型数据，其中，code表示执行结果代码，message表示执行结果详情，data表示待传递的数据，这里是查找到指定数值的行号、列表
        """
        try:
            workbook = xlrd.open_workbook(self.file_path)
            worksheet = workbook.sheet_by_name(sheet_name)
            column_lines = worksheet.ncols
            row_lines = worksheet.nrows
            # print(f"row: {row_lines}, column:{column_lines}")
            flag = True
            row, column = 0, 0
            while row < row_lines:
                while column < column_lines:
                    # print(f"current pos:{(row, column)}, value: {worksheet.cell(row, column).value}")
                    if worksheet.cell(row, column).value == str(value):
                        flag = False
                        break
                    column += 1
                if not flag:
                    break
                else:
                    row += 1
            if flag:
                result = {"code": "900", "message": "无法找到值的的位置", "data": (-1, -1)}
            else:
                result = {"code": "000", "message": "找到值的位置", "data": (row, column)}
        except Exception as e:
            logger.error(f"[{self.__class__.__name__}] {e}")
            result = {"code": "900", "message": "定位值得位置时出错", "data": (-1, -1)}
        return result

    def write_cell_data(self, sheet_name: str, row_pos: int, col_pos: int, cell_value: str):
        """
        向指定单元格写入数据
        :param sheet_name: sheet页名称
        :param row_pos: 指定单元格的行号，如1表示第一行，而非索引号
        :param col_pos: 指定单元格的列号
        :param cell_value: 待写入的数据
        :return:
        """
        try:
            workbook = load_workbook(self.file_path)
            worksheet = workbook[sheet_name]
            cell = worksheet.cell(row_pos, col_pos)
            cell.value = cell_value
            workbook.save(self.file_path)
            result = {"code": "000", "message": "向单元格写入数据成功", "data": []}
        except Exception as e:
            logger.error(f"{e}")
            logger.error(f"Failed to write cell data to excel file '{self.file_path}'")
            result = {"code": "900", "message": "向单元格写入数据失败", "data": []}
        return result


if __name__ == "__main__":
    excel = ExcelOperate(Path(DATA_PATH) / "test_case.xlsx")
    result = excel.locate_cell_position("login_logout", "flag")
    print(result)
